﻿/*
Commerce by Onex – eCommerce software platform (www.ecommerce.byonex.com)
Copyright (C) 2008-2010 by Onex Group

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 
 */
using System;
using System.Collections.Generic;
using System.Text;
using ShopModel;
using System.Data;
using System.Data.SqlClient;

namespace ShopDAO
{
    public class VoucherDAO
    {
        private string connectionString;

        private SqlConnection Connection()
        {
            return new SqlConnection(connectionString);
        }

        public VoucherDAO()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["csShop"].ConnectionString;
        }

        /// <summary>
        /// Pobiera informacje o kuponach rabatowych kupującego.
        /// (Gets information about the buyer’s Vourchers.)
        /// </summary>
        /// <param name="CustomerId">
        /// Unikalne ID kupującego.
        /// (Unique buyer ID.)
        /// </param>
        /// <returns>
        /// Zwraca listę obiektów Voucher zawierającą informacje o wszystkich kuponach rabatowych wybranego klienta. 
        /// (Returns list of Vourcher objects containing information about all discount vouchers for a selected customer.)
        /// </returns>
        public List<Voucher> GetCustomerVouchers(int CustomerId)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            List<Voucher> result = new List<Voucher>();

            command.CommandText = "GetCustomerVouchers";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@CustomerId", CustomerId));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();
                Voucher voucher = null;
                while (sqlR.Read())
                {
                    voucher = new Voucher();
                    voucher.IdVoucher = (int)sqlR["IdVoucher"];
                    voucher.Code = sqlR["Guid"].ToString().Replace("-", "").Substring(0, 8);
                    voucher.Type = (VoucherType)sqlR["Type"];
                    voucher.Date = (DateTime)sqlR["Date"];
                    voucher.Value = (sqlR["Value"] == DBNull.Value) ? null : (decimal?)sqlR["Value"];
                    voucher.AcceptValue = (decimal)sqlR["AcceptValue"];
                    voucher.CustomerId = (sqlR["CustomerId"] == DBNull.Value) ? null : (int?)sqlR["CustomerId"];
                    voucher.Used = (bool)sqlR["Used"];
                    voucher.UsedRealizationId = (sqlR["UsedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["UsedRealizationId"];
                    voucher.GeneratedRealizationId = (sqlR["GeneratedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["GeneratedRealizationId"];
                    result.Add(voucher);
                }

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Pobiera informacje o stworzonym kuponie rabatowym.
        /// (Gets information about the created discount voucher. )
        /// </summary>
        /// <param name="RealizationId">
        /// Unikalne ID zamówienia.
        /// (Unique order ID.)
        /// </param>
        /// <returns>
        /// Zwraca obiekt Voucher zawierający informacje o stworzonym kuponie rabatowym.
        /// (Returns object that contains information about the created discount voucher.)
        /// </returns>
        public Voucher GetGeneratedRealizationVoucher(int RealizationId)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            Voucher result = null;

            command.CommandText = "GetGeneratedRealizationVoucher";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@RealizationId", RealizationId));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();
                if (sqlR.Read())
                {
                    result = new Voucher();
                    result.IdVoucher = (int)sqlR["IdVoucher"];
                    result.Code = sqlR["Guid"].ToString().Replace("-", "").Substring(0, 8);
                    result.Type = (VoucherType)sqlR["Type"];
                    result.Date = (DateTime)sqlR["Date"];
                    result.Value = (sqlR["Value"] == DBNull.Value) ? null : (decimal?)sqlR["Value"];
                    result.AcceptValue = (decimal)sqlR["AcceptValue"];
                    result.CustomerId = (sqlR["CustomerId"] == DBNull.Value) ? null : (int?)sqlR["CustomerId"];
                    result.Used = (bool)sqlR["Used"];
                    result.UsedRealizationId = (sqlR["UsedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["UsedRealizationId"];
                    result.GeneratedRealizationId = (sqlR["GeneratedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["GeneratedRealizationId"];
                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Pobiera informacje o wykorzystanym kuponie rabatowym.
        /// (Gets information about the discount voucher.)
        /// </summary>
        /// <param name="RealizationId">
        /// Unikalne ID zamówienia.
        /// (Unique order ID.)
        /// </param>
        /// <returns>
        /// Zwraca obiekt Voucher zawierający informacje o wykorzystanym kuponie rabatowym.
        /// (Returns an object that contains information about the granted discount voucher.)
        /// </returns>
        public Voucher GetUsedRealizationVoucher(int RealizationId)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            Voucher result = null;

            command.CommandText = "GetUsedRealizationVoucher";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@RealizationId", RealizationId));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();
                if (sqlR.Read())
                {
                    result = new Voucher();
                    result.IdVoucher = (int)sqlR["IdVoucher"];
                    result.Code = sqlR["Guid"].ToString().Replace("-", "").Substring(0, 8);
                    result.Type = (VoucherType)sqlR["Type"];
                    result.Date = (DateTime)sqlR["Date"];
                    result.Value = (sqlR["Value"] == DBNull.Value) ? null : (decimal?)sqlR["Value"];
                    result.AcceptValue = (decimal)sqlR["AcceptValue"];
                    result.CustomerId = (sqlR["CustomerId"] == DBNull.Value) ? null : (int?)sqlR["CustomerId"];
                    result.Used = (bool)sqlR["Used"];
                    result.UsedRealizationId = (sqlR["UsedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["UsedRealizationId"];
                    result.GeneratedRealizationId = (sqlR["GeneratedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["GeneratedRealizationId"];
                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Pobiera informacje o wybranym kuponie rabatowym. 
        /// (Gets information about the selected voucher.)
        /// </summary>
        /// <param name="VoucherCode">
        /// Unikalny identyfikator Guid.
        /// (Unique Guid.)
        /// </param>
        /// <param name="Used">
        /// Obiekt bool lub wartość 'null'.  
        /// (Int object or 'null' value.)
        /// </param>
        /// <returns>
        /// Zwraca obiekt Voucher zawierający informacje o kuponie rabatowym.
        /// (Returns an object that contains information about the discount voucher.)
        /// </returns>
        public Voucher GetVoucherByGuid(string VoucherCode, bool? Used)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            Voucher result = null;

            command.CommandText = "GetVoucherByGuid";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@Code", VoucherCode.Replace("-", "").Substring(0, 8)));
            command.Parameters.Add(new SqlParameter("@Used", Used));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();
                if (sqlR.Read())
                {
                    result = new Voucher();
                    result.IdVoucher = (int)sqlR["IdVoucher"];
                    result.Code = sqlR["Guid"].ToString().Replace("-", "").Substring(0, 8);
                    result.Type = (VoucherType)sqlR["Type"];
                    result.Date = (DateTime)sqlR["Date"];
                    result.Value = (sqlR["Value"] == DBNull.Value) ? null : (decimal?)sqlR["Value"];
                    result.AcceptValue = (decimal)sqlR["AcceptValue"];
                    result.CustomerId = (sqlR["CustomerId"] == DBNull.Value) ? null : (int?)sqlR["CustomerId"];
                    result.Used = (bool)sqlR["Used"];
                    result.UsedRealizationId = (sqlR["UsedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["UsedRealizationId"];
                    result.GeneratedRealizationId = (sqlR["GeneratedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["GeneratedRealizationId"];
                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Zapisuje informacje o nowym kuponie rabatowym.
        /// (Saves information about the new discount voucher.)
        /// </summary>
        /// <param name="Voucher">
        /// Obiekt Voucher zawierający informacje o kuponie rabatowym.
        /// (Voucher object contains information about discount voucher.)
        /// </param>
        /// <returns>
        /// Zwraca obiekt Int. Jeśli zwrócona wartość jest równa '-1' wystąpił błąd z zapisam kuponu do bazy.
        /// (Returns Int object. If the returned value is equal ‘-1', error with saving discount voucher to the database has occurred.)
        /// </returns>
        public int SaveVoucher(Voucher Voucher)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;

            int result = -1;

            command.CommandText = "SaveVoucher";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@IdVoucher", Voucher.IdVoucher));
            command.Parameters.Add(new SqlParameter("@Type", Voucher.Type));
            command.Parameters.Add(new SqlParameter("@Date", Voucher.Date));
            command.Parameters.Add(new SqlParameter("@AcceptValue", Voucher.AcceptValue));
            command.Parameters.Add(new SqlParameter("@CustomerId", Voucher.CustomerId));
            command.Parameters.Add(new SqlParameter("@Used", Voucher.Used));
            command.Parameters.Add(new SqlParameter("@GeneratedRealizationId", Voucher.GeneratedRealizationId));
            command.Parameters.Add(new SqlParameter("@Value", Voucher.Value));
            command.Parameters.Add(new SqlParameter("@UsedRealizationId", Voucher.UsedRealizationId));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                if (sqlR.Read())
                    result = (int)sqlR["VoucherID"];

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Pobiera unikalne ID wszystkich rodzajów kuponów rabatowych z bazy. 
        /// (Gets unique ID of all types of discount vouchers from the database.)
        /// </summary>
        /// <returns>
        /// Zwraca tablicę obiektów Int zawierającą ID wszystkich rodzajów kuponów rabatowych.
        /// (Returns Int objects table containing ID of all types of discount vouchers.)
        /// </returns>
        public int[] GetAllVoucherTypesIDs()
        {
            List<int> result = null;
            SqlConnection connection = Connection();
            SqlCommand command = connection.CreateCommand();

            command.CommandText = "GetAllVoucherTypesIDs";
            command.CommandType = CommandType.StoredProcedure;

            result = new List<int>();
            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    result.Add(reader.GetInt32(0));
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { connection.Close(); }

            return result.ToArray();
        }

        /// <summary>
        /// Pobiera listę kuponów rabatowych (Downloads list of discount vouchers)
        /// </summary>
        /// <param name="pageNumber">Iliść stron na których wyświetlone zostaną rekordy (Number of pages where record will be displayed)</param>
        /// <param name="pageSize">Iliść rekordów wyświetlonych na stronie (Number of recors displayed on the page)</param>
        /// <param name="Count">Ilość rekordów zwruconych w zapytaniu(Number of record returned in query)</param>
        /// <param name="used">Czy użyty (Whether used or not)</param>
        /// <returns>Lista obiektów typu Voucher zawierajaca informacje o kuponie (List of Voucher objects containing information about the discount voucher)</returns>
        public List<Voucher> GetVouchers(ref int pageNumber, int pageSize, out int Count, bool used)
        {
            string query = string.Empty;
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            List<Voucher> result = new List<Voucher>();

            command.CommandText = "GetVouchers";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@page_size", pageSize));

            SqlParameter inout = new SqlParameter("@page_number", SqlDbType.Int);
            inout.Direction = ParameterDirection.InputOutput;
            inout.Value = pageNumber;
            command.Parameters.Add(inout);

            SqlParameter output = new SqlParameter("@vouchers_count", SqlDbType.Int);
            output.Direction = ParameterDirection.Output;
            command.Parameters.Add(output);

            query = string.Format("SELECT [IdVoucher],[Guid],[Type] ,[Date] ,[Value] ,[AcceptValue],[CustomerId],[Used] ,[GeneratedRealizationId],[UsedRealizationId] FROM [Vouchers] WHERE Used={0}", used ? 1 : 0);

            command.Parameters.Add(new SqlParameter("@query", query));
            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();
                Voucher voucher = null;
                while (sqlR.Read())
                {
                    voucher = new Voucher();
                    voucher.IdVoucher = (int)sqlR["IdVoucher"];
                    voucher.Code = sqlR["Guid"].ToString().Replace("-", "").Substring(0, 8);
                    voucher.Type = (VoucherType)sqlR["Type"];
                    voucher.Date = (DateTime)sqlR["Date"];
                    voucher.Value = (sqlR["Value"] == DBNull.Value) ? null : (decimal?)sqlR["Value"];
                    voucher.AcceptValue = (decimal)sqlR["AcceptValue"];
                    voucher.CustomerId = (sqlR["CustomerId"] == DBNull.Value) ? null : (int?)sqlR["CustomerId"];
                    voucher.Used = (bool)sqlR["Used"];
                    voucher.UsedRealizationId = (sqlR["UsedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["UsedRealizationId"];
                    voucher.GeneratedRealizationId = (sqlR["GeneratedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["GeneratedRealizationId"];
                    result.Add(voucher);
                }

                sqlR.Close();

                Count = (int)output.Value;
                pageNumber = (int)inout.Value;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                Count = 0;
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Tworzy nowy kupon rabatowy (Creates new discount voucher)
        /// </summary>
        /// <param name="Voucher">Obiekt zawierająch wszystkie informacje znajdujace sie w kuponie rabatowym (Object containing all information included in the discount voucher)</param>
        /// <param name="count">Ilość kuponow do utworzenia (Number of vouchers to be created)</param>
        /// <returns>Jesli operacja tworzenia zakonczona sukcesem zwraca 'true' (Returns ‘true’ if voucher creation succeeded)</returns>
        public bool GenerateVouchers(Voucher Voucher,int count)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();

            command.CommandText = "GenerateVouchers";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@Count", count));
            command.Parameters.Add(new SqlParameter("@Type", Voucher.Type));
            command.Parameters.Add(new SqlParameter("@AcceptValue", Voucher.AcceptValue));
            command.Parameters.Add(new SqlParameter("@Value", Voucher.Value));
            bool result = false;
            try
            {
                sqlC.Open();
                command.ExecuteNonQuery();
                result = true;   
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                result = false;
            }
            finally
            { sqlC.Close(); }

            return result;

        }

        /// <summary>
        /// Pobiera kupon rabatowy na podstawie jego ID (Downloads discount voucher on the basis of it ID)
        /// </summary>
        /// <param name="IDs">Id kuponu rabatowego (Discount voucher ID)</param>
        /// <returns>Lista obiektów typu Voucher zawierajaca informacje o kuponie (List of Voucher objects containing information about the voucher)</returns>
        public List<Voucher> GetVouchersByIDs(string IDs)
        {
            IDs = CustomerDAO.CheckIntegerList(IDs);
            string query = string.Empty;
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            List<Voucher> result = new List<Voucher>();

            command.CommandText = string.Format("SELECT [IdVoucher],[Guid],[Type] ,[Date] ,[Value] ,[AcceptValue],[CustomerId],[Used] ,[GeneratedRealizationId],[UsedRealizationId] FROM [Vouchers] WHERE IdVoucher IN ({0})", IDs);

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();
                Voucher voucher = null;
                while (sqlR.Read())
                {
                    voucher = new Voucher();
                    voucher.IdVoucher = (int)sqlR["IdVoucher"];
                    voucher.Code = sqlR["Guid"].ToString().Replace("-", "").Substring(0, 8);
                    voucher.Type = (VoucherType)sqlR["Type"];
                    voucher.Date = (DateTime)sqlR["Date"];
                    voucher.Value = (sqlR["Value"] == DBNull.Value) ? null : (decimal?)sqlR["Value"];
                    voucher.AcceptValue = (decimal)sqlR["AcceptValue"];
                    voucher.CustomerId = (sqlR["CustomerId"] == DBNull.Value) ? null : (int?)sqlR["CustomerId"];
                    voucher.Used = (bool)sqlR["Used"];
                    voucher.UsedRealizationId = (sqlR["UsedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["UsedRealizationId"];
                    voucher.GeneratedRealizationId = (sqlR["GeneratedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["GeneratedRealizationId"];
                    result.Add(voucher);
                }

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result; 
        }
        
        /// <summary>
        /// Pobiera ostatnio wygenerowane kupony (Gets last generated vouchers)
        /// </summary>
        /// <returns>Lista obiektów typu Voucher zawierajaca informacje o kuponie (List of Voucher objects containing information about the voucher)</returns>        
        public List<Voucher> GetLastGeneratedVouchers()
        {
            string query = string.Empty;
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            List<Voucher> result = new List<Voucher>();

            command.CommandText = "SELECT [IdVoucher],[Guid],[Type] ,[Date] ,[Value] ,[AcceptValue],[CustomerId],[Used] ,[GeneratedRealizationId],[UsedRealizationId] FROM [Vouchers] WHERE [date]=(select max(date) from vouchers)";

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();
                Voucher voucher = null;
                while (sqlR.Read())
                {
                    voucher = new Voucher();
                    voucher.IdVoucher = (int)sqlR["IdVoucher"];
                    voucher.Code = sqlR["Guid"].ToString().Replace("-", "").Substring(0, 8);
                    voucher.Type = (VoucherType)sqlR["Type"];
                    voucher.Date = (DateTime)sqlR["Date"];
                    voucher.Value = (sqlR["Value"] == DBNull.Value) ? null : (decimal?)sqlR["Value"];
                    voucher.AcceptValue = (decimal)sqlR["AcceptValue"];
                    voucher.CustomerId = (sqlR["CustomerId"] == DBNull.Value) ? null : (int?)sqlR["CustomerId"];
                    voucher.Used = (bool)sqlR["Used"];
                    voucher.UsedRealizationId = (sqlR["UsedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["UsedRealizationId"];
                    voucher.GeneratedRealizationId = (sqlR["GeneratedRealizationId"] == DBNull.Value) ? null : (int?)sqlR["GeneratedRealizationId"];
                    result.Add(voucher);
                }

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result; 
        }
    }
}
